<?php

class Reports extends Admin_Controller {

	//this is used when editing or adding a customer
	var $customer_id	= false;	

	function __construct()
	{		
		parent::__construct();

		$this->auth->check_access('Admin', true);
		
		$this->load->model('Order_model');
		$this->load->model('Search_model');
		$this->load->helper(array('formatting'));
		
		$this->lang->load('report');
	}
	
	function index()
	{
		$data['page_title']	= lang('reports');
		$data['years']		= $this->Order_model->get_sales_years();
		$this->view($this->config->item('admin_folder').'/reports', $data);
	}
	
	function best_sellers()
	{
		$start	= $this->input->post('start');
		$end	= $this->input->post('end');
		$data['best_sellers']	= $this->Order_model->get_best_sellers($start, $end);
		
		$this->load->view($this->config->item('admin_folder').'/reports/best_sellers', $data);	
	}
	
	function sales()
	{
		$year			= $this->input->post('year');
		//$data['orders']	= $this->Order_model->get_gross_monthly_sales($year); 
		$order = $this->Order_model->get_order_year_sales($year);
		if(!empty($order)){ 
			foreach($order as $item){
				$history  = json_decode($item->content);
				$shipping 		= !empty($history->shipping_location->total_price_shipping)?$history->shipping_location->total_price_shipping:0;
				$price_score    = !empty($history->score_customer->total_price_score)?$history->score_customer->total_price_score:0;
				$data_item = $orders[$item->month];
				$data_item["coupon_discount"] += $item->coupon_discount;
				$data_item["gift_card_discount"] += $item->gift_card_discount;
				$data_item["subtotal"] += $item->subtotal;
				$data_item["shipping"] += $shipping;
				$data_item["price_score"] += $price_score;
				$data_item["grand_total"] += $item->subtotal+$shipping-$price_score;
				$orders[$item->month] = $data_item;
			}
		}
		$data["year"] = $year;
		$data["orders"] = $orders;
		$this->load->view($this->config->item('admin_folder').'/reports/sales', $data);	
	}
	function sales_excel(){
		$post = $this->input->post();
		
		if(!empty($post["report_month_year"])){
			$this->load->library('excel');	
			$objPHPExcel = new PHPExcel();
			
			$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1,  "Tháng");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1,  "Giảm giá");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1,  "Quà tặng");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1,  "Tổng giá trị hàng hóa");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1,  "Tổng phí vận chuyển");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 1,  "Tổng số tiền trừ (Điểm qui đổi tiền)");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 1,  "Thu thực tế");		
			$row = 2;
			$i = 0;
			
			foreach($post["report_month_year"] as $month =>$val){
				$info_excel = json_decode($val);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row, $month);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$row, $info_excel->coupon_discount);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$row, $info_excel->gift_card_discount);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,$row, $info_excel->subtotal);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$row, $info_excel->shipping);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$row, $info_excel->price_score);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row, $info_excel->grand_total);
				$row++;
			}
			//format cell
			$objPHPExcel->getActiveSheet()->getStyle('B1:B'.$row)->getNumberFormat()->setFormatCode('#,##0');
			$objPHPExcel->getActiveSheet()->getStyle('C1:C'.$row)->getNumberFormat()->setFormatCode('#,##0');
			$objPHPExcel->getActiveSheet()->getStyle('D1:D'.$row)->getNumberFormat()->setFormatCode('#,##0');
			$objPHPExcel->getActiveSheet()->getStyle('E1:E'.$row)->getNumberFormat()->setFormatCode('#,##0');
			$objPHPExcel->getActiveSheet()->getStyle('F1:F'.$row)->getNumberFormat()->setFormatCode('#,##0');
			$objPHPExcel->getActiveSheet()->getStyle('G1:G'.$row)->getNumberFormat()->setFormatCode('#,##0');
						//format test
			$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setBold(true);
			
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':G'.$row)->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,"Tổng cộng");
			$objPHPExcel->getActiveSheet()->setCellValue("B".$row,"=SUM(B2:B".($row-1).")");
			$objPHPExcel->getActiveSheet()->setCellValue("C".$row,"=SUM(C2:C".($row-1).")");
			$objPHPExcel->getActiveSheet()->setCellValue("D".$row,"=SUM(D2:D".($row-1).")");
			$objPHPExcel->getActiveSheet()->setCellValue("E".$row,"=SUM(E2:E".($row-1).")");
			$objPHPExcel->getActiveSheet()->setCellValue("F".$row,"=SUM(F2:F".($row-1).")");
			$objPHPExcel->getActiveSheet()->setCellValue("G".$row,"=SUM(G2:G".($row-1).")");
			
			foreach(range('A','G') as $columnID) {$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);}
			//$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('F')->setWidth('10');
			$this->excel->setActiveSheetIndex(0);
			//name the worksheet
			$this->excel->getActiveSheet()->setTitle('');
			//set cell A1 content with some text
			$this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');
			//change the font size
			$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
			//$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			// Sending headers to force the user to download the file
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="bao_cao_nam.xls"');
			header('Cache-Control: max-age=0');
			$objWriter->save('php://output');
		}
	}
	function best_seller_excel(){
		$post = $this->input->post();
		if(!empty($post["best_seller_excel"])){
			$this->load->library('excel');	
			$objPHPExcel = new PHPExcel();
			
			$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
			$objPHPExcel->setActiveSheetIndex(0);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1,  "STT");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1,  "Mã hàng hóa");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1,  "Tên sản phẩm");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1,  "Số lượng đã bán");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1,  "Tổng tiền");
	
			$row = 2;
			$i = 0;
			
			foreach($post["best_seller_excel"] as $month =>$val){
				$i++;
				$info_excel = json_decode($val);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row, $i);
				$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, $row)->setValueExplicit($info_excel->sku, PHPExcel_Cell_DataType::TYPE_STRING);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$row, $info_excel->name);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,$row, $info_excel->quantity_sold);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$row, $info_excel->subtotal);
				$row++;
			}
			//format cell
			$objPHPExcel->getActiveSheet()->getStyle('E1:E'.$row)->getNumberFormat()->setFormatCode('#,##0');
						//format test
			$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFont()->setBold(true);
			
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':E'.$row)->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,"Tổng cộng");
			$objPHPExcel->getActiveSheet()->setCellValue("D".$row,"=SUM(D2:D".($row-1).")");
			$objPHPExcel->getActiveSheet()->setCellValue("E".$row,"=SUM(E2:E".($row-1).")");
			
			$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$row.':C'.$row);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':C'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			
			foreach(range('A','E') as $columnID) {$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);}
			//$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn('F')->setWidth('10');
			$this->excel->setActiveSheetIndex(0);
			//name the worksheet
			$this->excel->getActiveSheet()->setTitle('');
			//set cell A1 content with some text
			$this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');
			//change the font size
			$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
			//$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			// Sending headers to force the user to download the file
			
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="san_pham_da_ban.xls"');
			header('Cache-Control: max-age=0');
			$objWriter->save('php://output');
		}
	}
}